/* eslint-disable @typescript-eslint/no-explicit-any */
// 此文件是通用基类，需要处理任意表结构，合理使用 any 类型
// 具体 Service 继承时会有明确的类型约束

import { eq, and, inArray, count, type SQL } from 'drizzle-orm';
import type { PgTable } from 'drizzle-orm/pg-core';
import type { Database } from '../client';
import { db as globalDb } from '../client';
import { withAudit } from '../helpers/audit.helper';
import {
  paginate,
  buildPaginationMeta,
  paginateQueryBuilder,
  paginateWithSql,
  paginateFullSql,
} from '../helpers/pagination.helper';
import { AuditContextManager } from '../context/audit-context';
import type { IPaginationQuery, IPaginationResponse } from '@nbase/shared';

/**
 * Service 基类
 *
 * 提供标准的 CRUD 操作，自动处理审计字段
 *
 * 特性：
 * - 自动从 AuditContextManager 获取当前用户 ID
 * - 自动检测表有哪些审计字段
 * - 支持全局 db 或注入 db
 *
 * @example
 * @Injectable()
 * export class UserService extends BaseService<typeof sysUser> {
 *   constructor() {
 *     super(sysUser);  // ✅ 极简！使用全局 db
 *   }
 *
 *   // 或者注入 db（便于测试）
 *   constructor(@InjectDB() db: Database) {
 *     super(sysUser, db);
 *   }
 *
 *   // 直接使用继承的方法
 *   async createUser(dto) {
 *     return this.create(dto);  // ✅ 自动处理审计字段
 *   }
 * }
 */
export abstract class BaseService<TTable extends PgTable> {
  private _db: Database;

  constructor(
    protected readonly table: TTable,
    db?: Database  // ✅ 可选！默认使用全局 db
  ) {
    this._db = db || globalDb;
  }

  /**
   * 获取数据库实例
   */
  protected get db(): Database {
    return this._db;
  }

  /**
   * 获取当前用户 ID（从 AuditContextManager）
   */
  protected getUserId(): string | undefined {
    return AuditContextManager.getUserId();
  }

  /**
   * 添加软删除过滤条件
   * @private
   */
  private addSoftDeleteFilter(where?: SQL): SQL | undefined {
    const tableAny = this.table as any;

    if (!tableAny.isDeleted) {
      return where;
    }

    const isDeletedCondition = eq(tableAny.isDeleted, false);

    return where
      ? and(where, isDeletedCondition) as SQL
      : isDeletedCondition;
  }

  /**
   * 检查表是否支持软删除
   * @private
   */
  private supportsSoftDelete(): boolean {
    return !!(this.table as any).isDeleted;
  }

  /**
   * 查询所有记录（自动排除已删除）
   */
  async findAll(): Promise<any[]> {
    const where = this.addSoftDeleteFilter();

    return await this.db
      .select()
      .from(this.table as any)
      .where(where || undefined);
  }

  /**
   * 根据 ID 查询
   */
  async findById(id: string): Promise<any> {
    const [record] = await this.db
      .select()
      .from(this.table as any)
      .where(eq((this.table as any).id, id))
      .limit(1);
    return record;
  }

  /**
   * 根据条件查询单条记录（自动排除已删除）
   */
  async findOne(where: SQL): Promise<any> {
    const finalWhere = this.addSoftDeleteFilter(where);

    const [record] = await this.db
      .select()
      .from(this.table as any)
      .where(finalWhere || undefined)
      .limit(1);

    return record;
  }

  /**
   * 根据条件查询多条记录（自动排除已删除）
   *
   * @param where 查询条件
   * @param options 可选配置（排序、限制数量）
   * @returns 记录数组
   */
  async find(where: SQL, options?: { orderBy?: SQL; limit?: number }): Promise<any[]> {
    const finalWhere = this.addSoftDeleteFilter(where);

    let query = this.db
      .select()
      .from(this.table as any)
      .where(finalWhere || undefined);

    // 添加排序
    if (options?.orderBy) {
      query = query.orderBy(options.orderBy) as any;
    }

    // 添加限制
    if (options?.limit) {
      query = query.limit(options.limit) as any;
    }

    return await query;
  }

  /**
   * 根据 ID 数组查询多条记录（自动排除已删除）
   *
   * @param ids ID 数组
   * @returns 记录数组
   */
  async findByIds(ids: string[]): Promise<any[]> {
    if (ids.length === 0) {
      return [];
    }

    return this.find(inArray((this.table as any).id, ids));
  }

  /**
   * 统计记录数量（自动排除已删除）
   *
   * @param where 查询条件（可选）
   * @returns 记录数量
   */
  async count(where?: SQL): Promise<number> {
    const finalWhere = this.addSoftDeleteFilter(where);

    const [result] = await this.db
      .select({ count: count() })
      .from(this.table as any)
      .where(finalWhere || undefined);

    return Number(result.count);
  }

  /**
   * 检查记录是否存在（自动排除已删除）
   *
   * @param where 查询条件
   * @returns 是否存在
   */
  async exists(where: SQL): Promise<boolean> {
    const record = await this.findOne(where);
    return !!record;
  }

  /**
   * 创建记录（自动填充 createdBy）
   */
  async create(data: any): Promise<any> {
    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'create', data, userId);

    const [record] = await this.db
      .insert(this.table as any)
      .values(auditData)
      .returning();
    return record;
  }

  /**
   * 更新记录（自动填充 updatedAt, updatedBy）
   */
  async update(id: string, data: any): Promise<any> {
    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'update', data, userId);

    const [updated] = await this.db
      .update(this.table as any)
      .set(auditData)
      .where(eq((this.table as any).id, id))
      .returning();
    return updated;
  }

  /**
   * 批量更新（根据条件，自动填充 updatedAt, updatedBy）
   *
   * @param where 查询条件
   * @param data 更新数据
   * @returns 更新后的记录数组
   */
  async updateMany(where: SQL, data: any): Promise<any[]> {
    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'update', data, userId);

    return await this.db
      .update(this.table as any)
      .set(auditData)
      .where(where)
      .returning();
  }

  /**
   * 软删除（自动填充 isDeleted, updatedAt, updatedBy）
   * 如果表没有 isDeleted 字段，将执行物理删除
   */
  async softDelete(id: string): Promise<any> {
    if (!this.supportsSoftDelete()) {
      return this.hardDelete(id);
    }

    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'delete', {}, userId);

    const [deleted] = await this.db
      .update(this.table as any)
      .set(auditData)
      .where(eq((this.table as any).id, id))
      .returning();

    return deleted;
  }

  /**
   * 物理删除（慎用）
   */
  async hardDelete(id: string): Promise<any> {
    const [deleted] = await this.db
      .delete(this.table as any)
      .where(eq((this.table as any).id, id))
      .returning();
    return deleted;
  }

  /**
   * 批量软删除（根据条件，自动填充 isDeleted, updatedAt, updatedBy）
   *
   * @param where 查询条件
   * @returns 删除的记录数组
   */
  async softDeleteMany(where: SQL): Promise<any[]> {
    if (!this.supportsSoftDelete()) {
      return this.hardDeleteMany(where);
    }

    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'delete', {}, userId);

    return await this.db
      .update(this.table as any)
      .set(auditData)
      .where(where)
      .returning();
  }

  /**
   * 批量物理删除（慎用）
   *
   * @param where 查询条件
   * @returns 删除的记录数组
   */
  async hardDeleteMany(where: SQL): Promise<any[]> {
    return await this.db
      .delete(this.table as any)
      .where(where)
      .returning();
  }

  /**
   * 恢复软删除的记录（设置 isDeleted = false）
   *
   * @param id 记录 ID
   * @returns 恢复后的记录
   */
  async restore(id: string): Promise<any> {
    if (!this.supportsSoftDelete()) {
      throw new Error('此表不支持软删除，无法恢复');
    }

    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'update', { isDeleted: false }, userId);

    const [restored] = await this.db
      .update(this.table as any)
      .set(auditData)
      .where(eq((this.table as any).id, id))
      .returning();

    return restored;
  }

  /**
   * 批量恢复软删除的记录
   *
   * @param where 查询条件
   * @returns 恢复的记录数组
   */
  async restoreMany(where: SQL): Promise<any[]> {
    if (!this.supportsSoftDelete()) {
      throw new Error('此表不支持软删除，无法恢复');
    }

    const userId = this.getUserId?.();
    const auditData = withAudit(this.table, 'update', { isDeleted: false }, userId);

    return await this.db
      .update(this.table as any)
      .set(auditData)
      .where(where)
      .returning();
  }

  /**
   * 批量创建
   */
  async createMany(dataList: any[]): Promise<any[]> {
    const userId = this.getUserId?.();
    const auditDataList = dataList.map((data) =>
      withAudit(this.table, 'create', data, userId)
    );

    return await this.db
      .insert(this.table as any)
      .values(auditDataList)
      .returning();
  }

  /**
   * 分页查询（基础分页，查询数据 + 总数）
   *
   * @param query 分页参数
   * @param where 查询条件（可选）
   * @returns 分页结果（包含总数、总页数等）
   *
   * @example
   * // 基础分页
   * const result = await this.paginate({ page: 1, pageSize: 10 });
   *
   * // 带条件分页
   * const result = await this.paginate(
   *   { page: 1, pageSize: 10, sortBy: 'createdAt', sortOrder: 'desc' },
   *   eq(sysUser.status, 1)
   * );
   *
   * // 多条件分页
   * const result = await this.paginate(
   *   query,
   *   and(eq(sysUser.status, 1), like(sysUser.username, '%admin%'))
   * );
   */
  async paginate(
    query: IPaginationQuery = {},
    where?: SQL
  ): Promise<IPaginationResponse<any>> {
    const finalWhere = this.addSoftDeleteFilter(where);
    return paginate(this.db, this.table, query, finalWhere);
  }

  /**
   * 对已构建的 QueryBuilder 进行分页
   *
   * 适用于复杂查询场景（联表查询、子查询等）
   *
   * @param queryBuilder Drizzle QueryBuilder（已包含 select, from, join, where, orderBy）
   * @param query 分页参数
   * @param totalCount 总记录数（需要自己先查询）
   * @returns 分页结果
   *
   * @example
   * // 构建联表查询
   * const queryBuilder = this.db
   *   .select({
   *     id: sysUser.id,
   *     username: sysUser.username,
   *     roleName: sysRole.name
   *   })
   *   .from(sysUser)
   *   .leftJoin(sysUserRole, eq(sysUser.id, sysUserRole.userId))
   *   .leftJoin(sysRole, eq(sysUserRole.roleId, sysRole.id))
   *   .where(eq(sysUser.isDeleted, false))
   *   .orderBy(desc(sysUser.createdAt));
   *
   * // 查询总数
   * const total = await this.count();
   *
   * // 执行分页
   * return this.paginateQueryBuilder(queryBuilder, query, total);
   */
  async paginateQueryBuilder<T = any>(
    queryBuilder: any,
    query: IPaginationQuery = {},
    totalCount: number
  ): Promise<IPaginationResponse<T>> {
    return paginateQueryBuilder<T>(queryBuilder, query, totalCount);
  }

  /**
   * 使用原生 SQL 模板进行分页查询
   *
   * 适用于复杂的 SQL 查询（窗口函数、递归 CTE、JSON 聚合等）
   *
   * @param options SQL 配置
   * @returns 分页结果
   *
   * @example
   * import { sql } from 'drizzle-orm';
   *
   * // 复杂的聚合查询
   * const result = await this.paginateWithSql<UserWithStats>({
   *   select: sql`
   *     SELECT
   *       u.id,
   *       u.username,
   *       COUNT(DISTINCT p.id) as post_count
   *   `,
   *   from: sql`
   *     FROM sys_user u
   *     LEFT JOIN posts p ON u.id = p.author_id
   *   `,
   *   where: sql`u.is_deleted = false`,
   *   groupBy: sql`u.id, u.username`,
   *   orderBy: sql`post_count DESC`,
   *   query: { page: 1, pageSize: 10 }
   * });
   */
  async paginateWithSql<T = any>(options: {
    select: SQL;
    from: SQL;
    where?: SQL;
    groupBy?: SQL;
    having?: SQL;
    orderBy?: SQL;
    query: IPaginationQuery;
  }): Promise<IPaginationResponse<T>> {
    return paginateWithSql<T>(this.db, options);
  }

  /**
   * 使用完整 SQL 进行分页查询
   *
   * 适用于极其复杂的 SQL（需要手动提供数据查询和计数查询）
   *
   * @param dataSql 数据查询 SQL（不含 LIMIT OFFSET）
   * @param countSql 计数查询 SQL（返回 count 字段）
   * @param query 分页参数
   * @returns 分页结果
   *
   * @example
   * import { sql } from 'drizzle-orm';
   *
   * // CTE 递归查询
   * const dataSql = sql`
   *   WITH RECURSIVE menu_tree AS (
   *     SELECT *, 0 as level FROM sys_menu WHERE parent_id IS NULL
   *     UNION ALL
   *     SELECT m.*, t.level + 1
   *     FROM sys_menu m
   *     JOIN menu_tree t ON m.parent_id = t.id
   *   )
   *   SELECT * FROM menu_tree ORDER BY level, sort
   * `;
   *
   * const countSql = sql`
   *   WITH RECURSIVE menu_tree AS (
   *     SELECT * FROM sys_menu WHERE parent_id IS NULL
   *     UNION ALL
   *     SELECT m.* FROM sys_menu m JOIN menu_tree t ON m.parent_id = t.id
   *   )
   *   SELECT COUNT(*) as count FROM menu_tree
   * `;
   *
   * const result = await this.paginateFullSql<MenuItem>(dataSql, countSql, query);
   */
  async paginateFullSql<T = any>(
    dataSql: SQL,
    countSql: SQL,
    query: IPaginationQuery = {}
  ): Promise<IPaginationResponse<T>> {
    return paginateFullSql<T>(this.db, dataSql, countSql, query);
  }

  /**
   * 构建分页元数据
   *
   * 用于自定义查询后构建分页响应
   *
   * @param total 总记录数
   * @param page 当前页码
   * @param pageSize 每页数量
   * @returns 分页元数据（不含 items）
   *
   * @example
   * // 自定义复杂查询
   * const items = await this.db.select()...;
   * const total = await this.count(where);
   *
   * return {
   *   items,
   *   ...this.buildPaginationMeta(total, query.page, query.pageSize)
   * };
   */
  buildPaginationMeta(
    total: number,
    page: 1,
    pageSize: 10
  ): Omit<IPaginationResponse<any>, 'items'> {
    return buildPaginationMeta(total, page, pageSize);
  }
}


